<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*,java.io.*,java.text.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
			<p>Display the classes currently taken by student X</p>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						
						int countA = 0;
						int countB = 0;
						int countC = 0;
						int countD = 0;
						int countOthers = 0;
						double avgGPA = 0;
						int countAll = 0;
						if (action != null && action.equals("submit1")) {
							// Begin transaction
							conn.setAutoCommit(false);		
							//calculate A grade count
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT count_grade AS countA FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND quarter = ? AND year = ? AND grade IN ('A+','A','A-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							pstmt.setString(3, request.getParameter("quarter")); 
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year"))); 
							ResultSet rs = pstmt.executeQuery();
							while(rs.next())
								countA += rs.getInt("countA");
							
							//calculate B grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countB FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND quarter = ? AND year = ? AND grade IN ('B+','B','B-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							pstmt.setString(3, request.getParameter("quarter")); 
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year"))); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countB += rs.getInt("countB");
							
							//calculate C grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countC FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND quarter = ? AND year = ? AND grade IN ('C+','C','C-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							pstmt.setString(3, request.getParameter("quarter")); 
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year"))); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countC += rs.getInt("countC");
							
							//calculate D grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countD FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND quarter = ? AND year = ? AND grade = 'D'");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							pstmt.setString(3, request.getParameter("quarter")); 
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year"))); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countD += rs.getInt("countD");
							
							//calculate other grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countAll FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND quarter = ? AND year = ?");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							pstmt.setString(3, request.getParameter("quarter")); 
							pstmt.setInt(4, Integer.parseInt(request.getParameter("year"))); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countOthers += rs.getInt("countAll");
							countOthers = countOthers - countA -countB - countC - countD;
							
							
							conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("submit2")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//System.out.println("---------------------------------------------\n");
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT count_grade AS countA FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND grade IN ('A+','A','A-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							ResultSet rs = pstmt.executeQuery();
							while(rs.next())
								countA += rs.getInt("countA");
							//System.out.println("---------------------------------------------\n");
							//calculate B grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countB FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND grade IN ('B+','B','B-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countB += rs.getInt("countB");
							//System.out.println("---------------------------------------------\n");
							//calculate C grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countC FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND grade IN ('C+','C','C-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countC += rs.getInt("countC");
							
							//calculate D grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countD FROM grade WHERE course_number = ? AND faculty_name = ? " +
							    " AND grade = 'D'");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countD += rs.getInt("countD");
							
							//calculate other grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countAll FROM grade WHERE course_number = ? AND faculty_name = ? ");   
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countOthers += rs.getInt("countAll");
							countOthers = countOthers - countA -countB - countC - countD;
							//System.out.println("---------------------------------------------\n");
							conn.setAutoCommit(true);
						}
						
						
						if (action != null && action.equals("submit3")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT count_grade AS countA FROM grade WHERE course_number = ? " +
							    " AND grade IN ('A+','A','A-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							ResultSet rs = pstmt.executeQuery();
							while(rs.next())
								countA += rs.getInt("countA");
							
							//calculate B grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countB FROM grade WHERE course_number = ? " +
							    " AND grade IN ('B+','B','B-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countB += rs.getInt("countB");
							
							//calculate C grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countC FROM grade WHERE course_number = ? " +
							    " AND grade IN ('C+','C','C-')");
							pstmt.setString(1, request.getParameter("course_number")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countC += rs.getInt("countC");
							
							//calculate D grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countD FROM grade WHERE course_number = ? " +
							    " AND grade = 'D'");
							pstmt.setString(1, request.getParameter("course_number")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countD += rs.getInt("countD");
							
							//calculate other grade count
							pstmt = conn
									.prepareStatement("SELECT count_grade AS countAll FROM grade WHERE course_number = ?");   
							pstmt.setString(1, request.getParameter("course_number")); 
							rs = pstmt.executeQuery();
							while(rs.next())
								countOthers += rs.getInt("countAll");
							countOthers = countOthers - countA -countB - countC - countD;
							
							conn.setAutoCommit(true);
						}
						
						if (action != null && action.equals("submit4")) {
							// Begin transaction
							conn.setAutoCommit(false);
							//System.out.println("---------------------------------------------\n");
							PreparedStatement pstmt = conn
									.prepareStatement("SELECT count_grade, number_grade FROM grade, grade_conversion WHERE course_number = ? AND faculty_name = ? " +
							    " AND grade = letter_grade");
							pstmt.setString(1, request.getParameter("course_number")); 
							pstmt.setString(2, request.getParameter("faculty_name")); 
							ResultSet rs = pstmt.executeQuery();
							
							while(rs.next()){
								avgGPA += rs.getDouble("number_grade")*rs.getInt("count_grade");
								countAll += rs.getInt("count_grade");
							}
							if(countAll != 0){
								avgGPA = avgGPA / countAll;
								DecimalFormat df = new DecimalFormat("#.###");
								avgGPA = Double.parseDouble(df.format(avgGPA));
							}
							
							conn.setAutoCommit(true);
						}
						
						
						
						

						// Create the statement
						Statement statement = conn.createStatement();
						// get all the courses
						ResultSet rsCourse = statement.executeQuery("SELECT course_number FROM course");		
						CachedRowSet cachedCourse = new CachedRowSetImpl();
						cachedCourse.populate(rsCourse);
						
						// get all the faculty memebers
						ResultSet rsFaculty = statement.executeQuery("SELECT name FROM faculty");		
						CachedRowSet cachedFaculty = new CachedRowSetImpl();
						cachedFaculty.populate(rsFaculty);
						
				%>
				<table border="1">
					<tr>
						<%
							String courseNumber = null;
							String facultyName = null;
						    String quarter = null;
						    int year = 0;
						    if(request.getParameter("course_number")!=null) {
								courseNumber = request.getParameter("course_number");
						    }
						    if(request.getParameter("faculty_name")!=null) {
								facultyName = request.getParameter("faculty_name");
						    }
						    if(request.getParameter("quarter")!=null) {
						    	quarter = request.getParameter("quarter");
						    }
						    if(request.getParameter("year")!=null) {
						    	year = Integer.parseInt(request.getParameter("year"));
						    }
								
							
						%>
						<form action="report3a.jsp" method="POST">
							<input type="hidden" value="submit1" name="action">
							<td>Input course number, professor and quarter for grade distribution</td>
							<td>
								<select name ="course_number">
									<%while (cachedCourse.next()){ %>
									<option value="<%=cachedCourse.getString("course_number")%>" 
									 	<% if (courseNumber!=null && courseNumber.equals(cachedCourse.getString("course_number"))) out.print("selected='selected'"); %>>
										<%=cachedCourse.getString("course_number")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td>
								<select name = "faculty_name">
									<%while (cachedFaculty.next()){ %>
									<option value="<%=cachedFaculty.getString("name")%>" 
										<% if (facultyName!=null && facultyName.equals(cachedFaculty.getString("name"))) out.print("selected='selected'"); %>>
										<%=cachedFaculty.getString("name")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td>
								
								
								<select name="quarter">					
									<option value="Spring" <% if (quarter!=null && quarter.equals("Spring")) out.print("selected='selected'");%>>Spring</option>		
									<option value="Summer" <% if (quarter!=null && quarter.equals("Summer")) out.print("selected='selected'");%>>Summer</option>		
									<option value="Fall" <% if (quarter!=null && quarter.equals("Fall")) out.print("selected='selected'");%>>Fall</option>		
									<option value="Winter" <% if (quarter!=null && quarter.equals("Winter")) out.print("selected='selected'");%>>Winter</option>		
								</select>
							</td>
							
							<td>
								<input type="text" value="<%=year!=0?year:""%>" name="year" size="10">
							</td>

							<td><input type="submit" value="submit"></td>
						</form>
					</tr>

					<tr>
						<form action="report3a.jsp" method="POST">
							<input type="hidden" value="submit2" name="action">
							<td>Input course number and professor for grade distribution</td>
							<td>
								<select name ="course_number">
									<%cachedCourse.beforeFirst(); %>
									<%while (cachedCourse.next()){ %>
									<option value="<%=cachedCourse.getString("course_number")%>" 
									 	<% if (courseNumber!=null && courseNumber.equals(cachedCourse.getString("course_number"))) out.print("selected='selected'"); %>>
										<%=cachedCourse.getString("course_number")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td>
								<select name = "faculty_name">
									<%cachedFaculty.beforeFirst();%>
									<%while (cachedFaculty.next()){ %>
									<option value="<%=cachedFaculty.getString("name")%>" 
										<% if (facultyName!=null && facultyName.equals(cachedFaculty.getString("name"))) out.print("selected='selected'"); %>>
										<%=cachedFaculty.getString("name")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td><input type="submit" value="submit"></td>
						</form>
					</tr>
					
					<tr>
						<form action="report3a.jsp" method="POST">
							<input type="hidden" value="submit3" name="action">
							<td>Input course number for grade distribution</td>
							<td>
								<select name ="course_number">
									<%cachedCourse.beforeFirst(); %>
									<%while (cachedCourse.next()){ %>
									<option value="<%=cachedCourse.getString("course_number")%>" 
									 	<% if (courseNumber!=null && courseNumber.equals(cachedCourse.getString("course_number"))) out.print("selected='selected'"); %>>
										<%=cachedCourse.getString("course_number")%>
									</option>		
									<%} %>
								</select>
							</td>	
							<td><input type="submit" value="submit"></td>
						</form>
							
					</tr>
					
					<tr>
						<form action="report3a.jsp" method="POST">
							<input type="hidden" value="submit4" name="action">
							<td>Input Course number and professor for average GPA</td>
							<td>
								<select name ="course_number">
									<%cachedCourse.beforeFirst(); %>
									<%while (cachedCourse.next()){ %>
									<option value="<%=cachedCourse.getString("course_number")%>" 
									 	<% if (courseNumber!=null && courseNumber.equals(cachedCourse.getString("course_number"))) out.print("selected='selected'"); %>>
										<%=cachedCourse.getString("course_number")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td>
								<select name = "faculty_name">
									<%cachedFaculty.beforeFirst();%>
									<%while (cachedFaculty.next()){ %>
									<option value="<%=cachedFaculty.getString("name")%>" 
										<% if (facultyName!=null && facultyName.equals(cachedFaculty.getString("name"))) out.print("selected='selected'"); %>>
										<%=cachedFaculty.getString("name")%>
									</option>		
									<%} %>
								</select>
							</td>
							<td><input type="submit" value="submit"></td>
						</form>
					</tr>
					
					
				<% if (action != null && (action.equals("submit1") || action.equals("submit2") ||action.equals("submit3"))) {%>
						
				
					<tr>
						<th>A level grade</th>
						<th>B level grade</th>
						<th>C level grade</th>
						<th>D level grade</th>
						<th>other grades</th>
						
					</tr>
					
					<tr>
						<td><%=countA%></td>
						<td><%=countB%></td>
						<td><%=countC%></td>
						<td><%=countD%></td>
						<td><%=countOthers%></td>
					</tr>
				<%} %>	
				
				<% if (action != null && action.equals("submit4")) {%>
						<tr>
						<td>Average GPA : <%=countAll!=0?avgGPA:"No record found"%></td>
						</tr>
				<%} %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>